﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using DerptelligenceSite.Models.Erik;

namespace DerptelligenceSite.Models
{
    public static class MySqlErikDatabase
    {
        private static bool Query(String queryString, out MySqlConnection conn, out MySqlDataReader rdr)
        {
            try
            {
                MySqlConnection myConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["connstring"].ConnectionString);

                myConnection.Open();

                MySqlCommand mySqlCmd = myConnection.CreateCommand();
                mySqlCmd.CommandText = queryString;
                mySqlCmd.CommandType = CommandType.Text;
                MySqlDataReader Reader = mySqlCmd.ExecuteReader();

                conn = myConnection;
                rdr = Reader;
                return true;
            }
            catch
            {
                conn = null;
                rdr = null;
                return false;
            }
        }

        public static IEnumerable<BlogItem> GetBlogItems(UInt32? id = null)
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            List<BlogItem> items = new List<BlogItem>();
            if (!Query("SELECT * FROM erik_blog ORDER BY PublishDate DESC LIMIT 0, 10", out conn, out rdr))
                return new List<BlogItem> { };

            while (rdr.Read())
            {
                var ID = rdr["ID"];
                var publishdate = rdr["PublishDate"];
                var title = rdr["Title"];
                var html = rdr["HTML"];

                items.Add(new BlogItem()
                {
                    ID = (UInt32)ID,
                    PublishDate = (DateTime)publishdate,
                    Title = (String)title,
                    HTML = (String)html
                });
            }
            conn.Dispose();

            return items;
        }

        public static IEnumerable<CourseItem> GetCourses()
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            if (!Query("SELECT * FROM erik_courses", out conn, out rdr))
                return new List<CourseItem> { };

            List<CourseItem> items = new List<CourseItem>();
            if (rdr != null)
            {
                while (rdr.Read() != false)
                {
                    items.Add(new CourseItem() 
                    { 
                        ID = (UInt32)rdr["ID"],
                        Title = (String)rdr["Title"],
                        Description = (String)rdr["Description"] });
                }
            }
            conn.Dispose();

            return items;
        }

        public static IEnumerable<HomeworkItem> GetHomeworkItems(UInt32? id = null, UInt32? course = null)
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            String queryString;
            String whereString = "";
            if (id.HasValue || course.HasValue)
            {
                if (id.HasValue)
                    whereString += "ID = " + id.Value.ToString();
                if (course.HasValue)
                    whereString += (whereString.Length != 0 ? " AND " : "") + "CourseID = " + course.Value.ToString();
                queryString = "SELECT * FROM erik_homework " + " WHERE " + whereString + " ORDER BY PublishDate DESC LIMIT 0, 10";
            }
            else
                queryString = "SELECT * FROM erik_homework ORDER BY PublishDate DESC LIMIT 0, 10";
            if (!Query(queryString, out conn, out rdr))
                return new List<HomeworkItem>();

            List<HomeworkItem> items = new List<HomeworkItem>();
            if (rdr != null)
            {
                while (rdr.Read() != false)
                {
                    items.Add(new HomeworkItem()
                    {
                        ID = (UInt32)rdr["ID"],
                        Title = (String)rdr["Title"],
                        Description = (String)rdr["Description"],
                        Course = (UInt32)rdr["CourseID"],
                        PublishDate = (DateTime)rdr["PublishDate"],
                        DownloadLinkInternal = (String)rdr["DownloadLinkInternal"],
                        DownloadLinkExternal = (String)rdr["DownloadLinkExternal"]
                    });
                }

            }
            conn.Dispose();

            return items;
        }

        public static IEnumerable<SJChatLogItem> GetChatLogItems(UInt32? id = null)
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            String queryString;
            if (id.HasValue)
                queryString = String.Format("SELECT * FROM erik_sjchatlogs WHERE ID = {0} AND Public = 1 ORDER BY PublishDate DESC LIMIT 0, 10", id.Value);
            else
                queryString = "SELECT * FROM erik_sjchatlogs WHERE Public = 1 ORDER BY PublishDate DESC LIMIT 0, 10";

            if (!Query(queryString, out conn, out rdr))
                return new List<SJChatLogItem>();

            List<SJChatLogItem> items = new List<SJChatLogItem>();
            if (rdr != null)
            {
                while (rdr.Read() != false)
                {
                    items.Add(new SJChatLogItem()
                    {
                        ID = (UInt32)rdr["ID"],
                        Public = (Boolean)rdr["Public"],
                        PublishDate = (DateTime)rdr["PublishDate"],
                        UserName = (String)rdr["UserName"],
                        ChatLog = (string)rdr["ChatLog"]
                    });
                }

            }
            conn.Dispose();

            return items;
        }
    }
}